import pandas as pd # type: ignore
from sqlalchemy import create_engine # type: ignore
import time
start =time.perf_counter()
# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 

# CSV 文件路径
csv_file_path_cust_info = "D:\数据库\数据仓库任务\datasets-1\datasets\source_crm\cust_info.csv"
csv_file_path_prd_info = "D:\数据库\数据仓库任务\datasets-1\datasets\source_crm\prd_info.csv"
csv_file_path_sales_details = "D:\数据库\数据仓库任务\datasets-1\datasets\source_crm\sales_details.csv"
# 建立连接字符串
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
# 使用 SQLAlchemy 创建数据库连接
engine = create_engine(conn_string)

try:
    # Step 1: 读取 CSV 文件
    df_cust_info = pd.read_csv(csv_file_path_cust_info)
    df_prd_info = pd.read_csv(csv_file_path_prd_info)
    df_sales_details = pd.read_csv(csv_file_path_sales_details)
    # 给 DataFrame 添加自增主键列
    # df_cust_info.insert(0, 'id', range(1, 1 + len(df_cust_info)))
    # df_prd_info.insert(0, 'id', range(1, 1 + len(df_prd_info)))
    # df_sales_details.insert(0, 'id', range(1, 1 + len(df_sales_details)))
    # Step 2: 将 DataFrame 导入到 PostgreSQL 数据库
    df_cust_info.to_sql('crm_cust_info', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.crm_cust_info 表中")
    # 导入到 crm_prd_info 表中
    df_prd_info.to_sql('crm_prd_info', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.crm_prd_info 表中")

    df_sales_details.to_sql('crm_sales_details', engine, schema='datawarehouse', if_exists='append', index=False)
    print("数据成功导入到 datawarehouse.crm_sales_details 表中")
except Exception as e:
    print(f"导入数据时出错: {e}")
finally:
    # 关闭数据库连接
    engine.dispose()

end = time.perf_counter()
print('Running time: %s Seconds'%(end-start))